#!/usr/bin/env node

/**
 * 数据库迁移管理工具
 * 支持MySQL和SQLite数据库的up/down迁移操作
 */

const fs = require('fs');
const path = require('path');
const mysql = require('mysql2/promise');
const sqlite3 = require('sqlite3').verbose();
const { promisify } = require('util');

// 加载环境配置
require('dotenv').config();

class DatabaseMigrator {
  constructor() {
    this.migrationsPath = path.join(__dirname, 'migrations');
    this.dbType = process.env.DB_TYPE || 'mysql';
    this.connection = null;
  }

  async connect() {
    if (this.dbType === 'sqlite') {
      const dbPath = process.env.DB_PATH || './database/local.db';
      this.connection = new sqlite3.Database(dbPath);
      this.connection.run = promisify(this.connection.run.bind(this.connection));
      this.connection.all = promisify(this.connection.all.bind(this.connection));
    } else {
      this.connection = await mysql.createConnection({
        host: process.env.DB_HOST,
        port: process.env.DB_PORT,
        user: process.env.DB_USER,
        password: process.env.DB_PASSWORD,
        database: process.env.DB_NAME,
        multipleStatements: true
      });
    }

    // 创建迁移记录表
    await this.createMigrationsTable();
  }

  async createMigrationsTable() {
    const createTableSQL = this.dbType === 'sqlite' 
      ? `CREATE TABLE IF NOT EXISTS migrations (
          id INTEGER PRIMARY KEY AUTOINCREMENT,
          filename TEXT UNIQUE NOT NULL,
          executed_at DATETIME DEFAULT CURRENT_TIMESTAMP
        )`
      : `CREATE TABLE IF NOT EXISTS migrations (
          id INT PRIMARY KEY AUTO_INCREMENT,
          filename VARCHAR(255) UNIQUE NOT NULL,
          executed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
        )`;

    if (this.dbType === 'sqlite') {
      await this.connection.run(createTableSQL);
    } else {
      await this.connection.execute(createTableSQL);
    }
  }

  async getExecutedMigrations() {
    const query = 'SELECT filename FROM migrations ORDER BY executed_at';
    
    if (this.dbType === 'sqlite') {
      const rows = await this.connection.all(query);
      return rows.map(row => row.filename);
    } else {
      const [rows] = await this.connection.execute(query);
      return rows.map(row => row.filename);
    }
  }

  async recordMigration(filename) {
    const query = 'INSERT INTO migrations (filename) VALUES (?)';
    
    if (this.dbType === 'sqlite') {
      await this.connection.run(query, [filename]);
    } else {
      await this.connection.execute(query, [filename]);
    }
  }

  async removeMigrationRecord(filename) {
    const query = 'DELETE FROM migrations WHERE filename = ?';
    
    if (this.dbType === 'sqlite') {
      await this.connection.run(query, [filename]);
    } else {
      await this.connection.execute(query, [filename]);
    }
  }

  getMigrationFiles() {
    if (!fs.existsSync(this.migrationsPath)) {
      fs.mkdirSync(this.migrationsPath, { recursive: true });
      return [];
    }

    return fs.readdirSync(this.migrationsPath)
      .filter(file => file.endsWith('.js'))
      .sort();
  }

  async executeMigration(filename, direction = 'up') {
    const migrationPath = path.join(this.migrationsPath, filename);
    const migration = require(migrationPath);

    if (typeof migration[direction] !== 'function') {
      throw new Error(`Migration ${filename} does not have a ${direction} function`);
    }

    console.log(`Executing ${direction}: ${filename}`);
    await migration[direction](this.connection, this.dbType);
  }

  async up(targetMigration = null) {
    const allMigrations = this.getMigrationFiles();
    const executedMigrations = await this.getExecutedMigrations();
    
    const pendingMigrations = allMigrations.filter(migration => 
      !executedMigrations.includes(migration)
    );

    if (pendingMigrations.length === 0) {
      console.log('No pending migrations');
      return;
    }

    const migrationsToRun = targetMigration 
      ? pendingMigrations.filter(m => m <= targetMigration)
      : pendingMigrations;

    for (const migration of migrationsToRun) {
      try {
        await this.executeMigration(migration, 'up');
        await this.recordMigration(migration);
        console.log(`✅ Completed: ${migration}`);
      } catch (error) {
        console.error(`❌ Failed: ${migration}`, error.message);
        throw error;
      }
    }

    console.log(`\n🎉 Successfully executed ${migrationsToRun.length} migrations`);
  }

  async down(steps = 1) {
    const executedMigrations = await this.getExecutedMigrations();
    
    if (executedMigrations.length === 0) {
      console.log('No migrations to rollback');
      return;
    }

    const migrationsToRollback = executedMigrations
      .slice(-steps)
      .reverse();

    for (const migration of migrationsToRollback) {
      try {
        await this.executeMigration(migration, 'down');
        await this.removeMigrationRecord(migration);
        console.log(`✅ Rolled back: ${migration}`);
      } catch (error) {
        console.error(`❌ Failed to rollback: ${migration}`, error.message);
        throw error;
      }
    }

    console.log(`\n🔄 Successfully rolled back ${migrationsToRollback.length} migrations`);
  }

  async status() {
    const allMigrations = this.getMigrationFiles();
    const executedMigrations = await this.getExecutedMigrations();

    console.log('\n📊 Migration Status:');
    console.log('==================');

    if (allMigrations.length === 0) {
      console.log('No migration files found');
      return;
    }

    allMigrations.forEach(migration => {
      const status = executedMigrations.includes(migration) ? '✅' : '⏳';
      console.log(`${status} ${migration}`);
    });

    const pending = allMigrations.length - executedMigrations.length;
    console.log(`\nTotal: ${allMigrations.length}, Executed: ${executedMigrations.length}, Pending: ${pending}`);
  }

  async close() {
    if (this.connection) {
      if (this.dbType === 'sqlite') {
        this.connection.close();
      } else {
        await this.connection.end();
      }
    }
  }
}

// CLI 接口
async function main() {
  const command = process.argv[2];
  const arg = process.argv[3];

  const migrator = new DatabaseMigrator();

  try {
    await migrator.connect();

    switch (command) {
      case 'up':
        await migrator.up(arg);
        break;
      case 'down':
        const steps = parseInt(arg) || 1;
        await migrator.down(steps);
        break;
      case 'status':
        await migrator.status();
        break;
      case 'create':
        if (!arg) {
          console.error('Please provide a migration name');
          process.exit(1);
        }
        createMigrationFile(arg);
        break;
      default:
        console.log('Usage:');
        console.log('  node migrate.js up [target]     - Run pending migrations');
        console.log('  node migrate.js down [steps]    - Rollback migrations');
        console.log('  node migrate.js status          - Show migration status');
        console.log('  node migrate.js create <name>   - Create new migration file');
        break;
    }
  } catch (error) {
    console.error('Migration failed:', error.message);
    process.exit(1);
  } finally {
    await migrator.close();
  }
}

function createMigrationFile(name) {
  const timestamp = new Date().toISOString().replace(/[-:T]/g, '').split('.')[0];
  const filename = `${timestamp}_${name.replace(/\s+/g, '_').toLowerCase()}.js`;
  const filepath = path.join(__dirname, 'migrations', filename);

  const template = `/**
 * Migration: ${name}
 * Created: ${new Date().toISOString()}
 */

module.exports = {
  async up(connection, dbType) {
    // 实现向上迁移逻辑
    const sql = dbType === 'sqlite' 
      ? \`-- SQLite SQL here\`
      : \`-- MySQL SQL here\`;
    
    if (dbType === 'sqlite') {
      await connection.run(sql);
    } else {
      await connection.execute(sql);
    }
  },

  async down(connection, dbType) {
    // 实现向下迁移逻辑（回滚）
    const sql = dbType === 'sqlite'
      ? \`-- SQLite rollback SQL here\`
      : \`-- MySQL rollback SQL here\`;
    
    if (dbType === 'sqlite') {
      await connection.run(sql);
    } else {
      await connection.execute(sql);
    }
  }
};
`;

  fs.writeFileSync(filepath, template);
  console.log(`✅ Created migration file: ${filename}`);
}

if (require.main === module) {
  main();
}

module.exports = DatabaseMigrator;